/**
 * 
 */
package com.feib.stms.dao.jdbc;


import java.sql.Types;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.jdbc.support.incrementer.OracleSequenceMaxValueIncrementer;
import org.springframework.stereotype.Repository;

import com.feib.stms.dao.UploadFileTodoListDao;
import com.feib.stms.dao.jdbc.rowmapper.UploadFileTodoListRowMapper;
import com.feib.stms.model.UploadFileTodoList;
import com.feib.stms.model.User;
import com.feib.stms.pagination.PaginatedList;

/**
 * @author Jeff Gu
 *
 */
@Repository("uploadFileTodoListDao")
public class UploadFileTodoListJdbcDao extends BaseJdbcDao implements UploadFileTodoListDao 
{

	protected OracleSequenceMaxValueIncrementer sequence;

	protected SimpleJdbcInsert uploadFileTodoListJdbcInsert;
	
	@Override
	protected void initDao() throws Exception {
		super.initDao();
		this.sequence = new  OracleSequenceMaxValueIncrementer(this.getDataSource(), "UPLOAD_FILE_TODO_LIST_PK_SEQ");
		this.uploadFileTodoListJdbcInsert = new  SimpleJdbcInsert(this.getDataSource());
		this.uploadFileTodoListJdbcInsert.withTableName("UPLOAD_FILE_TODO_LIST")
			.usingColumns("UPLOAD_FILE_TODO_LIST_PK", "UPLOAD_FILE_PK", "GROUP_PK", "MENU_ITEM_PK", "SECURITIES_NO");
	}
	
	public int addUploadFileTodoList(UploadFileTodoList uploadFileTodoList) {
		MapSqlParameterSource parameters = new MapSqlParameterSource();

		Long uploadFileTodoListPk = new Long(sequence.nextLongValue());
		parameters.addValue("UPLOAD_FILE_TODO_LIST_PK",  uploadFileTodoListPk, Types.NUMERIC);
		parameters.addValue("UPLOAD_FILE_PK",  uploadFileTodoList.getUploadFile().getUploadFilePk(), Types.NUMERIC);
		parameters.addValue("GROUP_PK",  uploadFileTodoList.getGroup().getGroupPk(), Types.NUMERIC);
		parameters.addValue("MENU_ITEM_PK",  uploadFileTodoList.getMenuItem().getMenuItemPk(), Types.NUMERIC);
		parameters.addValue("SECURITIES_NO",  uploadFileTodoList.getSecuritiesNo(), Types.VARCHAR);
				
		int updateCounts = uploadFileTodoListJdbcInsert.execute(parameters);		

		if (updateCounts > 0)
			uploadFileTodoList.setUploadFileTodoListPk(uploadFileTodoListPk);

		return updateCounts;
	}


	public SqlQueryMapProvider getSqlQueryMapProviderByUploadFileTodoList(User user,PaginatedList paginatedList){

		StringBuffer sql = new StringBuffer();


		/* 取得user可以處理的券商類別 */
		String securitiesNo = user.getDealSecuritiesNos();
		if(StringUtils.isEmpty(securitiesNo)){
			securitiesNo="xxxxx,xxxx";
		}
		String[] securitiesNos = securitiesNo.split(",");	
		logger.debug("securitiesNo "+securitiesNo);
		List<String> listSecuritiesNo = Arrays.asList(securitiesNos);

		Map<String, Object> parameters = new HashMap<String, Object>();
		sql.append("select ");
		sql.append(" MI.ACTION_NAME AS TODO_ACTION_NAME,MO.ACTION_NAME AS PARENT_MENU_ITEM_ACTION_NAME," );
		sql.append(" MI.ACTION_PAGE AS TARGET_ACTION_PAGE,COUNT(*) AS COUNT ");
		sql.append(" FROM UPLOAD_FILE_TODO_LIST TODO,MENU_ITEM MI,MENU_ITEM MO  ");
		sql.append(" WHERE TODO.GROUP_PK = :T_GROUP_PK   ");
		sql.append(" AND MI.MENU_ITEM_PK=MO.MENU_ITEM_PK ");
		parameters.put("T_GROUP_PK",  user.getGroup().getGroupPk());

		if(listSecuritiesNo.size()==1){
			sql.append(" AND  TODO.SECURITIES_NO = :U_SECURITIES_NO  "); 
			parameters.put("U_SECURITIES_NO", listSecuritiesNo.get(0));
		}else{
			sql.append(" AND  TODO.SECURITIES_NO IN (:U_SECURITIES_NO  )"); 
			parameters.put("U_SECURITIES_NO", listSecuritiesNo);
		}
		sql.append(" AND EXISTS(");
		sql.append(" SELECT 1 FROM  APP_ROLE_MENU RM, APP_USER_ROLE UR ");
		sql.append(" WHERE MI.MENU_ITEM_PK = TODO.MENU_ITEM_PK ");
		sql.append(" AND MI.MENU_ITEM_PK = RM.MENU_ITEM_PK ");
		sql.append(" AND RM.ROLE_PK = UR.ROLE_PK ");
		sql.append(" AND UR.USER_PK = :UR_USER_PK )");

		parameters.put("UR_USER_PK",  user.getUserPk());
		
        /* 同一份交割款資料，同一主管不能執行審查作業又執行放行作業。也就是審查主管與放行主管不可為同一人；且此主管最多只能審查一次or放行一次此交割款資料。 */
		/* 所以該人員審查後 放行不要顯示在該人員待辦事項 */
		sql.append(" AND  NOT EXISTS(");
		sql.append(" SELECT 1 FROM  UPLOAD_FILE_ACTION_LOG L ");
		sql.append(" WHERE L.UPLOAD_FILE_PK = TODO.UPLOAD_FILE_PK ");		
		sql.append(" AND L.USER_PK = :L_USER_PK )");
	
		parameters.put("L_USER_PK",  user.getUserPk());
		
		
		
		
		sql.append("  GROUP BY MI.ACTION_NAME,MO.ACTION_NAME,MI.ACTION_PAGE ");

		if (null != paginatedList)
		{
			if (StringUtils.isNotEmpty(paginatedList.getSortCriterion()))
			{
				sql.append(" ORDER BY ").append(paginatedList.getSortCriterion()).append(" ").append( 2==paginatedList.getSortDirection() ? "ASC" : "DESC" );
			}			
		}
		
		logger.debug("SQL: {}", sql.toString());
		logger.debug("Args: {}", parameters);
	
		SqlQueryMapProvider sqlQueryMapProvider = new SqlQueryMapProvider(sql.toString(), parameters);
		return sqlQueryMapProvider;

	}



	public PaginatedList<UploadFileTodoList> queryUploadFileTodoList(User user, PaginatedList paginatedList) {
		SqlQueryMapProvider sqlQueryMapProvider =getSqlQueryMapProviderByUploadFileTodoList(user, paginatedList);
		return this.queryForPaginatedList(sqlQueryMapProvider, new UploadFileTodoListRowMapper(), paginatedList.getPageNumber(), paginatedList.getPageSize());

	}

	public int delete(Long uploadFilePk) {
		/* 查詢 SQL */
		StringBuffer sql = new StringBuffer();
		/* 查詢條件 */
		MapSqlParameterSource parameters = new MapSqlParameterSource();
		sql.append("DELETE FROM UPLOAD_FILE_TODO_LIST WHERE UPLOAD_FILE_PK = :UPLOAD_FILE_PK");
		parameters.addValue("UPLOAD_FILE_PK", uploadFilePk, Types.NUMERIC);
		int updateCount = this.getSimpleJdbcTemplate().update(sql.toString(), parameters);
		return updateCount;
	}
	


}
